package com.sample.javabean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.sample.action.form.PersinfoForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 参合登记信息类
 * @author 演示
 *
 */
public class Persinfo {

	private static Log LOG =LogFactory.getLog(Persinfo.class);
	//定义属性
	private String	cardNo	;//	身份证号
	private String	persName	;//	姓名
	private String	sex	;//	性别
	private String	age	;//	年龄
	private String	address	;//	家庭住址
	private String	tel	;//	联系方式
	private String	joinYear	;//	参合时间
	private String	invNumber	;//	参合发票
	private String	persNumber	;//	参合证号
	private String illname;//疾病名称
	private String illrecordname;//记录的慢性病名称

	private JdbcUtils jdbcUtils ;

	public Persinfo(){
		jdbcUtils=new JdbcUtils();
	}
	/**
	 * 参合登记
	 * @param form
	 */
	public void add(PersinfoForm form){
		Connection conn =null;
		Statement stmt =null;
		try{
			conn =jdbcUtils.getConn();
			stmt =conn.createStatement();
			//构造SQL语句
			String sql="insert into t_persinfo(cardNo, persName, sex, age, address, tel, joinYear, invNumber, persNumber) " +
					"values('"+form.getCardNo()+"', '"+form.getPersName()+"', '"+form.getSex()+"'," +
					" '"+form.getAge()+"', '"+form.getAddress()+"', '"+form.getTel()+"', '"+
					form.getJoinYear()+"', '"+form.getInvNumber()+"', '"+form.getPersNumber()+"')";
			int c =stmt.executeUpdate(sql);
		}catch (Exception e) {
			e.printStackTrace();
			LOG.error("record persinfo failed!", e);

		}finally{
			jdbcUtils.close(conn, stmt);

		}

	}
	/**
	 * 根据身份证号+年度查找是否存在参合记录
	 * @param cardNo
	 * @param runyear
	 * @return
	 */
	public boolean checkJoined(String cardNo,int runyear){
		boolean flag=false;
		Connection conn =jdbcUtils.getConn();
		Statement stmt =null;
		ResultSet rs =null;
		try{
			stmt =conn.createStatement();
			//构造SQL
			String sql="select * from t_persinfo where cardNo='"+cardNo+"' and joinYear='"+runyear+"'";
			rs =stmt.executeQuery(sql);
			if(rs.next()){
				flag=true;
			}
		}catch (Exception e) {
			e.printStackTrace();
			flag=false;
		}finally{
			jdbcUtils.close(null, stmt,rs);

		}
		return flag;  	 
	}

	/**
	 * 查询添加参合信息
	 * @return
	 */
	public List<Persinfo> findAll(){
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Persinfo> persinfoList =new ArrayList<Persinfo>(20);
		try{
			conn =jdbcUtils.getConn();
			stmt =conn.createStatement();
			//构造SQL语句
			String sql="select * from t_persinfo where 1=1";
			rs =stmt.executeQuery(sql);
			while(rs.next()){
				String cardNo=rs.getString("cardNo");
				String persName =rs.getString("persName");
				String sex =rs.getString("sex");
				String age =rs.getString("age");
				String address =rs.getString("address");
				String tel=rs.getString("tel");
				String joinYear =rs.getString("joinYear");
				String invNumber =rs.getString("invNumber");
				String persNumber =rs.getString("persNumber");
				//转为为Persion对象，并存储到persinfoList;
				Persinfo model=new Persinfo(cardNo, persName,sex,age,address,tel,joinYear,invNumber,persNumber);
				//调用顺序列表的add方法，加入元素
				persinfoList.add(model);

			}

		}catch (Exception e) {
			e.printStackTrace();
			LOG.error("load Role list failed!", e);
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return persinfoList;

	}
	/**
	 * 校验慢性病是否参合
	 * @param cardNo
	 * @return
	 */
	public String checkmxs(String cardNo) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		String aa = "0";
		String c=cardNo;
		int f=0;		
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			//查询慢性病信息表中这个身份证号
			String sql = "select * from t_mbbill where cardNo='" + c+ "'";
			System.out.println("cardNo=" + cardNo);
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				this.illname = rs.getString("illname");
			}else{
				f=50;
			}
			System.out.println("illname=" + illname);
			//查询就诊信息表中这个身份证
			String sql1 = "select * from t_mxill_message where cardNo='"
					+ c + "'";
			rs1 = stmt.executeQuery(sql1);
			if (rs1.next()) {
				illrecordname = rs1.getString("illname");
			}else{
				f=50;
			}
			System.out.println("illrecordname=" + illrecordname);
			if(f!=50){
				if (illname.equals(illrecordname)) {
					f=1;
				}
				System.out.println("aa=" +aa);

				String sql2="select * from t_bx_message where cardNo='"+cardNo+"'";
				rs2 = stmt.executeQuery(sql2);
				if(rs2.next()){
					f=3;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt, rs);
		}
		if (f==0)
		{
			return "0";//疾病名称不对应
		}
		else if(f==1)
		{
			return "1";//成功
		}
		else if(f==50)
		{
			return "0";//失败
		}
		else
		{
			return  "3";//已经报销过
		}

	}

	/**
	 * 
	 * 加载参合记录
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_persinfo where persNumber='"+this.persNumber+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.cardNo =rs.getString("cardNo");
				this.persName =rs.getString("persName");
				this.sex =rs.getString("sex");
				this.age =rs.getString("age");
				this.address =rs.getString("address");
				this.tel =rs.getString("tel");
				this.joinYear =rs.getString("joinYear");
				this.invNumber =rs.getString("invNumber");
				this.persNumber =rs.getString("persNumber");
			}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载信息失败！");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}

	/**
	 * 编辑参合信息
	 */
	public void editPersinfo() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "update  t_persinfo set cardNo='"+this.cardNo+"',persName='"+this.persName+"',sex='"+this.sex+"',age='"+this.age+"',address='"+this.address+"',tel='"+this.tel+"',joinYear='"+this.joinYear+"',invNumber='"+this.invNumber+"',persNumber='"+this.persNumber+"' where persNumber='"+this.persNumber+"'";
			System.out.println("sss");
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 删除参合信息
	 */
	public void delPersinfo() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_persinfo  where persNumber='"+this.persNumber+"'";
			// 4:执行SQL并返回结果
			System.out.println("sss");
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 分页显示参合信息
	 * @param page
	 * @param size
	 * @param form
	 * @return
	 */
	public Pager<Persinfo> list(int page,int size,PersinfoForm form) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<Persinfo> results=new Pager<Persinfo>();
		results.setPage(page);
		results.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_persinfo where 1=1";
			//查询参合人员姓名
			if(form!=null&&form.getPersName()!=null&&!"".equals(form.getPersName())){
				selectSql +=" and persName like'%"+form.getPersName()+"%'";
			}
			//查询身份证号
			if(form!=null&&form.getCardNo()!=null&&!"".equals(form.getCardNo())){
				selectSql +=" and cardNo like'%"+form.getCardNo()+"%'";
			}
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//分页SQL
			String pageSql=selectSql+" limit "+start+","+limit;
			rs= stmt.executeQuery(countSql);
			int total=0;
			while(rs.next()){
				total=rs.getInt("c");
			}
			results.setTotal(total);
			//查询分页信息
			rs= stmt.executeQuery(pageSql);
			while(rs.next()){


				String cardNo=rs.getString("cardNo");
				String persName =rs.getString("persName");
				String sex =rs.getString("sex");
				String age =rs.getString("age");
				String address =rs.getString("address");
				String tel=rs.getString("tel");
				String joinYear =rs.getString("joinYear");
				String invNumber =rs.getString("invNumber");
				String persNumber =rs.getString("persNumber");


				Persinfo model=new Persinfo();
				model.setCardNo(cardNo);
				model.setPersName(persName);
				model.setSex(sex);
				model.setAge(age);
				model.setAddress(address);
				model.setTel(tel);
				model.setJoinYear(joinYear);
				model.setInvNumber(invNumber);
				model.setPersNumber(persNumber);


				results.getDataSets().add(model);
			}
			results.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return results;


	}

	//get/set方法
	public String getCardNo() {
		return cardNo;
	}

	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getPersName() {
		return persName;
	}

	public void setPersName(String persName) {
		this.persName = persName;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getAge() {
		return age;
	}

	public void setAge(String age) {
		this.age = age;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getTel() {
		return tel;
	}

	public void setTel(String tel) {
		this.tel = tel;
	}

	public String getJoinYear() {
		return joinYear;
	}

	public void setJoinYear(String joinYear) {
		this.joinYear = joinYear;
	}

	public String getInvNumber() {
		return invNumber;
	}

	public void setInvNumber(String invNumber) {
		this.invNumber = invNumber;
	}

	public String getPersNumber() {
		return persNumber;
	}

	public void setPersNumber(String persNumber) {
		this.persNumber = persNumber;
	}
	public String getIllname() {
		return illname;
	}
	public void setIllname(String illname) {
		this.illname = illname;
	}
	public String getIllrecordname() {
		return illrecordname;
	}
	public void setIllrecordname(String illrecordname) {
		this.illrecordname = illrecordname;
	}
	//构造方法
	public Persinfo(String cardNo, String persName, String sex, String age,
			String address, String tel, String joinYear, String invNumber,
			String persNumber) {
		super();
		this.cardNo = cardNo;
		this.persName = persName;
		this.sex = sex;
		this.age = age;
		this.address = address;
		this.tel = tel;
		this.joinYear = joinYear;
		this.invNumber = invNumber;
		this.persNumber = persNumber;
	}



}
